前面有說過要執行效能調校，需要把焦點放在某些查詢上。

因此我們需要一種方法，能夠準確衡量查詢效能。

最後當我們用各種方式去改善效能時，也需要能夠擷取相關指標，去了解改善是否有效，這就是量化。

要擷取這個指標有很多種方法，在這世界上一定不只我等等寫的那三種，但這三種是我用過效果最好、最準確、最容易實作的，所以其他的方法也可以，不一定要照我的作法。

擷取的各種方法

有很多種方法可以做，據我所知有以下幾種

• Include Client Statistics
• 連線屬性
• SET STATISTICS TIME/IO
• 執行計畫中的 QueryTimeStats
• Query Store
• 動態管理檢視表（Dynamic Management Views，DMVs）
• Trace Events（Profiler）
• Extended Events

標示紅字的，是我認為最好的三種。這三種會重點說明，其餘的稍為帶過。

Include Client Statistics

這是實際上並不準確的衡量方式。除了這個以外，其他的方法基本上都是準確的。

可以在 SSMS 的查詢視窗中按右鍵，然後從快顯功能表選擇 「Include Client Statistics」 來啟用這個功能。

它會從你執行查詢的用戶端端點，擷取執行時間與 I/O 資訊。這表示網路時間、本機端資源競爭，以及所有其他可能因素，都會對效能衡量結果造成負面影響。

但是，這些數值的輸出結果，和其他方法所輸出的結果完全無法對應。

因此，我基本上從來不使用它。

連線屬性

這個方法其實非常方便，雖然我不會把它當作主要的衡量方式。

在你執行查詢之前，甚至是執行查詢之後，都可以在 SSMS 的查詢視窗中按右鍵，然後選屬性。

在那裡會看到連線細節資訊。這些詳細資訊包含連線耗用時間，也就是連線經過時間。它其實是衡量查詢效能的一個準確指標。

雖然你也可以在 SSMS 畫面底部看到查詢效能資訊，但那裡的時間精度只到秒。這裡的數值則可以精確到毫秒。

唯一的問題是，它不會提供 I/O 的衡量資料。因此，它不像其他同時提供 elapsed time、I/O，某些情況下還提供 CPU 使用量的衡量方式那麼有用。

SET STATISTICS TIME / IO

這是查詢調校時用來衡量效能的經典機制。

這些是準確的，所以如果你選擇使用它們，也沒有問題。

-- 用這個打開
SET STATISTICS TIME ON;

但是我要說明為何我很少使用他

1. 你只會得到單次衡量結果。根據我的經驗，如果我把同一個查詢執行多次，然後取平均值，會得到更準確的結果。但用這個方法很難做到這件事。

2. 得不到 CPU 使用量

3. 如果用這個方法同時擷取 TIME 和 IO，IO 的擷取實際上可能會對 TIME 的擷取造成負面影響，讓時間衡量變得比較不準確。通常你只會在執行時間低於 1 秒，甚至低於 100 毫秒的查詢上看到這種情況。

   當我們最需要準確度的時候，往往就是在調校最後那幾毫秒的時候。這種準確度受到干擾的情況，會降低這個方法的實用性。

但是，我還是會在一種情況下回頭使用這個衡量方式：當我需要針對查詢中涉及的各個物件，取得更細緻的 I/O 衡量資料時。

執行計畫中的 QueryTimeStats

當透過執行計畫擷取執行階段指標時，在 SSMS 中這稱為實際執行計畫。從 SQL Server 2016 開始，你可以取得查詢執行階段指標，其中也包含等待統計資訊。

這些都是準確的衡量資料，而且它們本身就是執行計畫的一部分。我絕對會使用這些資訊。

不過，同樣，他只會顯示單次執行的結果，因此很難取得平均值。

另外，擷取執行計畫會對時間衡量造成負面影響，因為在擷取執行計畫的同時擷取執行階段指標，會讓查詢執行變慢。

基於這個原因，當我想要取得準確的效能指標時，我不會擷取執行計畫。

這個簡單的事實表示，我無法一直依賴這種衡量方式。

Trace Events（Profiler）

這點會有點爭議

很多資深的工程師，已經用這個東西很長一段時間了，而且把這東西用的出神入化，這東西用起來也沒有什麼問題，我不是在說他們不好，可以繼續用沒有關係。

不過，基於以下幾個原因，我並不主張使用 Trace Events，如果是初學效能調校，那麼我強烈建議不要使用這個。

1. Trace Events 在 SQL Server 作業系統內部的成本非常高。
   擴充事件從 SQL Server 2008 開始，就被直接整合進 SQL Server 的內部行為中。相較之下，Trace Events 是另外建立、另外實作的機制。這導致它們相較於擴充事件，會額外產生更多記憶體與 CPU 的負擔。
   由於 Trace Events 擷取資訊的方式，它不像擴充事件一樣能在擷取階段就先進行篩選。這表示擷取某個事件所需的所有資源都會先被消耗，之後才進行篩選，然後再把該事件從結果中移除。

2. 另一個問題是 Profiler GUI。
   如果你把 Profiler GUI 連到正式伺服器上的 Trace Event，它會在該伺服器上建立額外的記憶體空間，並使用這些資源來即時讀取事件，進而搶走系統資源。

3. 最後，Trace Events 讀取資料的方式，也不如擴充事件中的監看即時資料視窗有效率。

   基於以上所有原因，在任何 SQL Server 2012 或更新版本的系統上，我不會使用 Trace Events 來擷取資訊。
   在 SQL Server 2012 以前，Trace Events 仍然是較偏好的機制。

DMVs 動態管理檢視表

這個在基礎那邊有講過，這是一個 SQL Server 用來檢視、蒐集資訊使用的表，他會有函數也有 view 但之後都會統稱 DMVs。

DMV 大致可以分成兩類

• 針對目前正在執行的查詢
• 針對先前已經執行完成的查詢

值得注意的是，先前已經執行完成的查詢所蒐集到的資訊，完全依賴快取，如果因為老化遭到移除，那這些指標也會跟著消失。

此外，先前已執行查詢的資訊只會是彙總資料。你沒有辦法分辨某個查詢是在凌晨 2 點執行，還是同一個查詢是在凌晨 3 點執行，除非你在兩個時間點都使用 DMV 擷取指標。

我推薦使用 DMV 來取得查詢指標的原因 :

1. SQL Server 2005 以後的每一個 SQL Server 版本，大多數 DMV 都可以使用，包括 AWS RDS、Google Cloud SQL，以及 Azure SQL Database。這表示我幾乎永遠都有方法取得查詢效能指標。
2. 我不一定會在每台伺服器上隨時啟用其他方法。有些資料庫負載可能太重，不適合啟用 Query Store。Extended Events 雖然非常輕量，但它也不是完全沒有成本，所以我也不一定會一直讓它執行。

但透過 DMV，我永遠都可以快速查看查詢指標。

擷取目前正在執行的查詢

把 DMV 想像成樂高，我們可以用不同的方式把他們組合起來

有一些起點是經常使用的話就會熟悉的，例如現在要說的 sys.dm_exec_requests

這個 DMV 會擷取大量關於目前正在執行查詢的資訊，包括 :

• start_time：查詢開始執行的時間
• command：查詢的命令類型，也就是它是哪一種查詢
• plan_handle：用來從快取中取得執行計畫
• sql_handle：用來從快取中取得 T-SQL 文字
• blocking_session_id：如果處理程序被封鎖，這個欄位會顯示是哪個 session 正在封鎖它
• wait_type：如果處理程序正在等待，這個欄位會顯示它正在等待什麼
• total_elapsed_time：目前為止，這個處理程序已經執行多久
• cpu_time：這個處理程序已經消耗多少 CPU 時間
• reads：這個處理程序已經執行多少次讀取
• writes：這個處理程序已經執行多少次寫入

利用這個 DMV 通常還需要把他和其他 DMV 結合起來。

1. sys.dm_exec_query_plan()，這包含目前存在於 plan cache 中的所有執行計畫
2. sys.dm_exec_sql_text()，保存正在執行查詢的 T-SQL batch 文字

例如以下寫法

SELECT C.text          AS [T-SQL 文字],
       B.query_plan    AS [執行計畫],
       A.cpu_time       AS [CPU 時間毫秒],
       A.logical_reads  AS [邏輯讀取次數],
       A.writes         AS [寫入次數]
FROM sys.dm_exec_requests AS A
     CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS B
     CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS C;

擷取先前已經執行的查詢

若要查看先前已經執行過的查詢，我們有許多不同的起點可以選擇
要用這個看到的前提是查詢還存在plan cache裡。

最常見的起點是 : sys.dm_exec_query_stats

• sql_handle：用來取得該 batch 的 T-SQL 文字
• plan_handle：用來取得該查詢的執行計畫
• last_execution_time：這個查詢最後一次執行的時間
• execution_count：這個查詢已經執行了多少次
• total_logical_reads：累積的邏輯讀取次數
• last_logical_reads：這個查詢最後一次執行時的讀取次數
• avg_logical_reads：這個查詢所有執行次數的平均讀取次數

一樣可以做各種組合

SELECT dest.text                  AS [T-SQL 文字],
       deqp.query_plan            AS [執行計畫],
       deqs.execution_count       AS [執行次數],
       deqs.min_logical_writes    AS [最小邏輯寫入次數],
       deqs.max_logical_reads     AS [最大邏輯讀取次數],
       deqs.total_logical_reads   AS [總邏輯讀取次數],
       deqs.total_elapsed_time    AS [總經過時間微秒],
       deqs.last_elapsed_time     AS [最後一次經過時間微秒]
FROM sys.dm_exec_query_stats AS deqs
     CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
     CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest;

另外，也有針對特定物件類型的 DMV：

• sys.dm_exec_procedure_stats：運作方式類似 sys.dm_exec_query_stats，但只針對已儲存的預存程序
• sys.dm_exec_function_stats：概念相同，但針對使用者定義函數
• sys.dm_exec_trigger_stats：回傳觸發程序效能的彙總資訊

透過這些 DMV，你可以取得目前仍在快取中的查詢所需資訊，用來呈現彙總後的查詢效能。

Query Store

這是在 SQL Server 2016 引入的。

他是以資料庫為單位啟用，用彙總的方式擷取查詢指標，然後把這些指標存在啟用 Query Store 資料庫內。

這些彙總資料會用時間去切分；預設是 60 分鐘。

由於 Query Store 包含非常多資訊，日後會專門寫一篇來詳細說明。

Extended Events 擴充事件

這最早是 2008 引入的，但當時功能還不足

後來 2012 最這東西升級。不只讓他可以用來擷取查詢指標，而且成為擷取詳細查詢校能資料最有效率的方式。

Extended Events 由多種不同的程式化結構組成；不過，我們會盡量用最簡單的方式來使用它們。基於這點，我們會把重點放在以下項目：

• Sessions：定義要擷取哪些事件、如何儲存這些事件、事件彼此之間的關聯，以及 Extended Events 行為的其他各個層面。
• Events：SQL Server 內部發生的活動。我們主要會聚焦在查詢效能相關事件，但它也包含等待、重新編譯、死結等事件。
• Global Fields（Actions）：可以附加到特定事件上的額外資訊。它們代表 SQL Server 內部額外的功能行為，因此必須審慎使用。
• Event Fields：每個事件都由一組標準資訊與可選資訊組成，這些資訊會被擷取下來。這是讓 Extended Events 如此有用的關鍵資料。
• Predicates：簡單來說，就是可以加入 Extended Events 的 WHERE 條件，用來篩選要擷取的資訊。
• Targets：定義擷取到的資料要儲存在哪裡。預設是 Ring Buffers，也就是 Windows 中的一段記憶體空間；但在正式環境中，應盡可能避免使用它。

實際操作 擴充事件 ( GUI )

首先，擴充事件 GUI 有兩種建立方式，精靈跟一般的

不建議去選精靈的他少了很多功能。

進到一般的新增工作階段後，名稱是必填的，要取一個看得懂的名字

然後他的範本可以用，我舉例一些比較好用的範本

• 計算查詢鎖定：這個範本會使用 histogram Target 搭配 query hash value，用來計算每個查詢的鎖定次數。
• TSQL：這是其中一個 Profiler Equivalent 範本。它會擷取 login，以及 batch 和遠端程序呼叫（RPC）完成事件。
• 查詢批次取樣：這是 Extended Events 專用的範本，會使用篩選器，只擷取所有執行中 session 的 20% 查詢指標。
• 查詢詳細資料追蹤：擷取各種 statement、batch 和 RPC completion，同時排除系統查詢與系統資料庫。
• 查詢等候統計資料：另一個只適用於 Extended Events 的範本。它會擷取所有查詢的開始與完成事件，也會擷取所有 statement，以及所有 session 中 20% 的內部與外部 waits，並包含 Causality Tracking。

任何一個 Session 都至少必須加入一個 Event。

任何一個 Session 都可以包含大量 Events。不過，我建議在把 Events 加入 Session 時要謹慎，因為如果加入過多 Events，可能會讓系統負載過高，進而嚴重影響系統效能。

當你第一次開啟 Events 頁面，而且沒有選擇任何範本時，畫面應該會類似下面這個圖

Events 可以篩選，類別目錄、封裝這些都預設就好不要改，只有通道這裡有一個 debug，這個不建議去動他，這是有風險的，反正就都預設就好

↑　點任何一個事件下面都可以看到詳細資訊，點兩下可以加到選取事件。

這次示範我選兩個 rpc_completed、sql_batch_completed。

到這裡為止其實已經可以按下確定去建立擴充事件了。

但是還有更多東西可以調，所以先去點右上角的設定

這裡會有三個頁籤，全域欄位( 動作 )、篩選 ( 述詞 )、事件欄位。

全域欄位 ( 動作 )

這是可以加入到事件的額外資料。

我這邊加入兩個查詢指標

• query_hash
• query_plan_hash

這兩個數值可以視為查詢的 “指紋”。

篩選 ( 述詞 )

這個分頁很像查詢中的 where 子句。

Extended Events 會根據你提供的 Predicate，在事件被擷取時就先篩選事件。

這表示要盡可能使用最嚴格的篩選條件。同時，也要確保越嚴格的篩選條件越早套用，這樣可以降低擷取事件時的額外負擔。

對於效能來說這很重要。

像我這個就是先設定，我只抓 AdventureWorks 然後只擷取執行超過 1000微秒的查詢。

事件欄位

在這邊可以去選要關閉或開啟哪些欄位

關掉的話可以降低擷取事件的時候的效能負擔

在下一步是定義資料存放區

預設情況下不一定要定義這個，當建立這個工作的時候，輸出結果會寫到一個稱為 ring buffers 的記憶體空間。

不過，這實際上會從作業系統和 SQL Server 拿走記憶體資源。因此，在正式環境中，或是在非正式環境中進行某些特定測試時，使用 Ring Buffers 完全不是好主意。

• etw_classic_sync_target：輸出到 Windows 事件追蹤（Event Tracing for Windows，ETW）。這很少使用，但如果你需要，它是可用的。
• event_counter：計算 Event 發生的次數。這是一種簡單方式，用來追蹤被監控系統上某個 Event 發生的頻率。它實際上會輸出到作業系統的 events collection。
• event_file：把所有擷取到的 Events 輸出到檔案。這是最常用的擷取機制。
• Histogram：這個 Target 允許你指定某個 Event，然後指定一個 Action 或 Field，用來對次數進行分組統計。這非常有用。
• pair_matching：你可以定義一種機制，用來配對成對的 Events。這個功能使用起來有些困難，而且沒有 Causality Tracking 那麼方便。
• ring_buffer：前面已經說明過的預設記憶體空間。再次強調，在正式環境中應該非常謹慎地使用它。

所以我會示範我平常用的 event_file 跟 histogram。

Event_file

如果選這個，那畫面會變這樣

必須提供 Events 輸出用的檔案名稱與路徑。

如果你是在 Azure SQL Database 上執行，輸出必須放到 Azure Storage，並且你必須確保安全性設定允許你的 Azure SQL Database instance 輸出資料。

在 AWS RDS 中，檔案只有一個可用位置：
D:\rdsdbdata\log

預設檔案大小是 1GB，太大太小都很麻煩，最好的狀況是設定 5~20 GB。

檔案換用就是檔案滿了的話要不要新增一個新的檔案繼續寫。

一般來說，這是管理 擴充事件所收集資料的最佳方式

輸出到檔案對系統造成的負載最低。擁有這些檔案也代表可以在需要的時候保存資料，而且永遠可以在監看即時資料視窗中開啟這些檔案。

Histogram

這個非常有用，這是一種很方便的方式，可以單純計算某個指定事件發生的次數，但同時又可以依照 fields 或 actions 來分組。

選擇要篩選的事件之後，可以選動作或是欄位。

像我選這個欄位就代表，統計每個 sp 被呼叫完成的次數。

原因追蹤

有一個東西在一般的那個畫面沒講到

排成那個沒什麼很字面意思，要說的是原因追蹤

這是一個很強大的功能。

它可以讓我們很容易識別彼此相關的事件。更進一步來說，他還會顯示這些事件發生的精確順序。

這在某些環境下非常有用，我最常用的地方是 :
排查重新編譯
因為我可以同時擷取查詢指標和重新編譯事件，然後把他們關連起來。

這個原因追蹤是工作層級的設定，但是當然啟用的話也會對擴充事件造成一點效能負擔。

然後就可以案確定新增工作。

最後有一個進階那個沒有講是因為那是用來控制擴充事件的效能的基本上預設的就可以。

實際操作 擴充事件 ( T-SQL )

想要用 T-SQL 也是可以，好處是可以帶著到處走

但它能做到的事情跟 GUI 基本上是一樣的，下面示範的是在 GUI 那邊做的用 T-SQL 做的話是什麼語法。

CREATE EVENT SESSION [Query Performance Metrics]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (WHERE (
              [sqlserver].[database_name] = N'AdventureWorks'
              AND [duration] > (1000)
           )
    ),
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE (
              [sqlserver].[database_name] = N'AdventureWorks'
              AND [duration] > (1000)
           )
    )
    ADD TARGET package0.event_file
    (SET filename = N'Query Performance Metrics');

語法很直觀，但你說要背我也背不起來，可以用GUI 產一次語法然後存下來

下面是開啟和關閉的語法

ALTER EVENT SESSION [Query Performance Metrics]
ON SERVER
STATE = START;

ALTER EVENT SESSION [Query Performance Metrics]
ON SERVER
STATE = STOP;

最後也可以用 T-SQL 去讀取結果

SELECT fx.object_name AS [事件名稱],
       fx.file_name   AS [檔案名稱],
       fx.event_data  AS [事件資料]
FROM sys.fn_xe_file_target_read_file('.\Query Performance Metrics_*.xel',
                                     NULL,
                                     NULL,
                                     NULL) AS fx;

最後有一個好工具 DBATools，他提供一系列 scripts 和機制，可以用來處理擴充事件的 XML，但是要自己去花時間研究，在這裡不提。

喔對 event_data 這是 XML，會包含每個 Event 所擷取到的所有資訊，阿 XML 很難讀，如果不用這個工具會看到頭很痛，當然還有一個 GUI 的辦法叫做監看即時資料。

即時監看資料

因為擴充事件輸出的是 XML，所以很難看，我知道有高手會用 XQuery 來處理，但這不是很容易。

因此，我通常都依賴即時監看資料視窗來讀取這些擴充事件的輸出結果。

有個小技巧，可以把下面的詳細資料轉移到上面欄位去看，這樣比較好看

監看資料有很多種查看方式，他的花樣很多，用到的時候再說

但有一個功能我可以先講 : 彙總

我很常會遇到 平均而言，這個查詢在 時間內完成這種情境

為了達到這個平均而言，就需要用監看資料的匯總功能。

首先要彙總的話，監看資料要先給他暫停才能選，然後還要先建立群組去給彙總依據

然後就可以選彙總

擴充事件注意事項

儘管這是一個很好用的工具，對整個 instance 影響也很小，但沒有任何東西是完全沒有成本的，我有一些實務上的”建議”，可以參考 :

• 適當設定最大檔案大小。
• 避免 debug events。
• 避免使用 No_Event_Loss。

適當設定最大檔案大小

檔案的預設值是 1GB。當你考慮到 Extended Events 可以收集到的資訊量時，這其實非常小。建議把這個數值設定得更高一些，大約在 5 到 20GB 之間，確保你有足夠的空間擷取資訊，而且不會在 buffer 填滿時，還要等待檔案子系統幫你建立檔案。這可能會導致 event loss。

不過，這還是取決於你的系統。如果你很清楚自己預期會產生多少輸出資料，就可以依照自己的環境，把檔案大小設定得更合適。

避免 debug events

Extended Events 不只提供一種機制，可以觀察 SQL Server 及其內部行為，而且觀察能力遠超過以前 Trace Events 能做到的程度；Microsoft 也使用相同功能作為 SQL Server 疑難排解的一部分。

有一些 events 與 SQL Server debugging 相關。這些預設不會透過 wizard 顯示，但你可以透過 T-SQL command 存取它們，而且也有方法可以在 Session editor 視窗的 channel selection 中啟用它們。

如果沒有 Microsoft 的直接指導，不要使用它們。它們可能會變更，而且是供 Microsoft 內部使用的。如果你真的覺得需要實驗，必須特別注意任何包含 break action 的 events。

這表示如果該 event 被觸發，它會讓 SQL Server 停在導致 event 觸發的那一行程式碼上。也就是說，你的 server 會完全離線，而且處於未知狀態。如果你在正式環境這樣做，可能會導致重大中斷。它也可能造成資料遺失以及 database corruption。

避免使用 No_Event_Loss

Extended Events 的設計方式，就是某些 events 會遺失。這是設計上的行為，而且非常可能發生。

但是在設定 session 時，你可以使用一個叫做 No_Event_Loss 的設定。如果你在已經有負載的系統上這樣做，可能會看到系統承受明顯額外負載，因為你實際上是在告訴系統，不管後果如何，都要保留 buffer 中的資訊。

對於小型、集中，而且針對特定行為的 sessions，這種做法可能可以接受。